package com.sdgakj.repository;

import com.sdgakj.entity.ShtCity;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

public interface ShtCityDao extends PagingAndSortingRepository<ShtCity, Long>, JpaSpecificationExecutor<ShtCity>
{
    
    @Query(value = "select * from sht_cities where id =?1", nativeQuery = true)
    ShtCity getCityById(Long id);
    
    @Query(value = "select * from sht_cities where id in(select distinct seecityid from sht_truck where  carstatus in ('2','3') and deltag ='0' group by seecityid) order by convert(name using gbk)", nativeQuery = true)
    List<ShtCity> findAllCitysByTruck();
    
    @Query(value = "select * from sht_cities where id in(select distinct city from sht_purchase_truck where deltag ='0' group by city) order by convert(name using gbk)", nativeQuery = true)
    List<ShtCity> findAllCitysByPurchaseTruck();

    // 根据 车系查询
    @Query(value = "select * from sht_cities where id in(select distinct seecityid from sht_truck where deltag ='0' and carstatus in('2','3') and seriesid in(select id from sht_truck_series where title in ('斯太尔', '金王子', 'HOKA', '新黄河', 'SITRAK'))group by seecityid) order by convert(name using gbk)", nativeQuery = true)
    List<ShtCity> findAllCitysBySeries();
    
    @Query(value = "select distinct(c.name) from sht_cities c where (c.name like %?1% or c.first_letter like %?1%)", nativeQuery = true)
    List<String> getAllCitys(String city);
    
    @Query(value="select c.* from sht_cities c where c.id in (select city_id from jcc_detect_factory )",nativeQuery=true)
    List<ShtCity> findCitys4Factory();

//    @Query(value = "select * from sht_cities where id in (select  distinct city from sht_coupon)",nativeQuery = true)
//    List<ShtCity> findCitysByCoupon();
    
    @Query(value="select * from sht_cities where id in(select city_id from jcc_detect_factory where province_id=?1)",nativeQuery=true)
    List<ShtCity> findCities4Factory(int cid);
    
    /**查询已发布车辆所在城市*/
    @Query(value="select c.id,c.name,c.first_letter,c.pid from(select DISTINCT seecityid from sht_truck where carstatus='2' and seecityid is not null ORDER BY seecityid) tc LEFT JOIN sht_cities c on tc.seecityid=c.id ",nativeQuery=true)
    List<ShtCity> getTruckCitys();
    
    /**查询重汽已发布车辆所在城市*/
    @Query(value="select c.id,c.name,c.first_letter,c.pid from(select DISTINCT seecityid from sht_truck where carstatus in ('2','3') and seecityid is not null and seriesid in(select id from sht_truck_series where title in ('斯太尔', '金王子', 'HOKA', '新黄河', 'SITRAK')) ORDER BY seecityid) tc LEFT JOIN sht_cities c on tc.seecityid=c.id ",nativeQuery=true)
    List<ShtCity> getZQTruckCitys();

    /******************************below use for manager dictionary interface****************************/
	
	@Query(value = "select distinct city from sht_purchase_truck where deltag ='0'", nativeQuery = true)
	List<Integer> findAllCitysIdsByPurchaseTruck();
	
	@Query(value = "select distinct seecityid from sht_truck where  carstatus in ('2','3') and deltag ='0'", nativeQuery = true)
	List<Integer> findAllCityIdsByTruck();
	
	// 根据 车系查询
	@Query(value = "select distinct seecityid from sht_truck where deltag ='0' and carstatus in('2','3') and seriesid in (?1)", nativeQuery = true)
	List<Integer> findAllCityIdsBySeries(List<Integer> seriesIds);

    @Query(value="select city_id from jcc_detect_factory where province_id=?1",nativeQuery=true)
    List<Integer> findCitieIds4Factory(int cid);

    /**查询已发布车辆所在城市*/
    @Query(value="select DISTINCT seecityid from sht_truck where carstatus='2' and seecityid is not null ORDER BY seecityid",nativeQuery=true)
    List<Integer> getTruckCityIds();

    /**查询重汽已发布车辆所在城市*/
    @Query(value="select DISTINCT seecityid from sht_truck where carstatus in ('2','3') and seecityid is not null and seriesid in(?1) ORDER BY seecityid",nativeQuery=true)
    List<Integer> getZQTruckCityIds(List<Integer> seriesIds);
}
